#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库表结构验证脚本
验证所有新创建的优化表是否正确建立
"""

import sqlite3
import os
from datetime import datetime

def get_db_path():
    """获取数据库路径"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    backend_dir = os.path.dirname(current_dir)
    db_path = os.path.join(backend_dir, 'instance', 'dispatch_system.db')
    return db_path

def verify_table_exists(cursor, table_name):
    """验证表是否存在"""
    cursor.execute("""
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name=?
    """, (table_name,))
    
    return cursor.fetchone() is not None

def get_table_info(cursor, table_name):
    """获取表结构信息"""
    cursor.execute(f"PRAGMA table_info({table_name})")
    return cursor.fetchall()

def count_table_records(cursor, table_name):
    """统计表记录数"""
    try:
        cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
        return cursor.fetchone()[0]
    except Exception:
        return 0

def verify_system_dictionaries(cursor):
    """验证系统字典表"""
    print("\n📋 验证 system_dictionaries 表...")
    
    if not verify_table_exists(cursor, 'system_dictionaries'):
        print("❌ system_dictionaries 表不存在")
        return False
    
    # 检查字段结构
    columns = get_table_info(cursor, 'system_dictionaries')
    expected_columns = ['id', 'dict_type', 'dict_key', 'dict_label', 'dict_value', 
                       'sort_order', 'is_active', 'is_system', 'parent_id', 
                       'description', 'created_at', 'updated_at']
    
    actual_columns = [col[1] for col in columns]
    missing_columns = set(expected_columns) - set(actual_columns)
    
    if missing_columns:
        print(f"❌ 缺少字段: {missing_columns}")
        return False
    
    # 统计记录数
    record_count = count_table_records(cursor, 'system_dictionaries')
    print(f"✅ system_dictionaries 表结构正确，包含 {record_count} 条记录")
    
    # 检查字典类型分布
    cursor.execute("""
        SELECT dict_type, COUNT(*) as count 
        FROM system_dictionaries 
        GROUP BY dict_type 
        ORDER BY dict_type
    """)
    
    dict_types = cursor.fetchall()
    print("   字典类型分布:")
    for dict_type, count in dict_types:
        print(f"     - {dict_type}: {count} 项")
    
    return True

def verify_material_categories(cursor):
    """验证材料分类表"""
    print("\n📦 验证 material_categories 表...")
    
    if not verify_table_exists(cursor, 'material_categories'):
        print("❌ material_categories 表不存在")
        return False
    
    # 检查字段结构
    columns = get_table_info(cursor, 'material_categories')
    expected_columns = ['id', 'name', 'code', 'parent_id', 'level', 'path', 
                       'sort_order', 'is_active', 'description', 'created_at', 'updated_at']
    
    actual_columns = [col[1] for col in columns]
    missing_columns = set(expected_columns) - set(actual_columns)
    
    if missing_columns:
        print(f"❌ 缺少字段: {missing_columns}")
        return False
    
    # 统计记录数
    record_count = count_table_records(cursor, 'material_categories')
    print(f"✅ material_categories 表结构正确，包含 {record_count} 条记录")
    
    # 检查层级分布
    cursor.execute("""
        SELECT level, COUNT(*) as count 
        FROM material_categories 
        GROUP BY level 
        ORDER BY level
    """)
    
    levels = cursor.fetchall()
    print("   分类层级分布:")
    for level, count in levels:
        print(f"     - 第{level}级: {count} 个分类")
    
    # 检查materials表是否添加了category_id字段
    if verify_table_exists(cursor, 'materials'):
        materials_columns = get_table_info(cursor, 'materials')
        materials_column_names = [col[1] for col in materials_columns]
        if 'category_id' in materials_column_names:
            print("   ✅ materials表已添加category_id字段")
        else:
            print("   ❌ materials表缺少category_id字段")
    
    return True

def verify_form_field_templates(cursor):
    """验证表单字段模板表"""
    print("\n📝 验证 form_field_templates 表...")
    
    if not verify_table_exists(cursor, 'form_field_templates'):
        print("❌ form_field_templates 表不存在")
        return False
    
    # 检查字段结构
    columns = get_table_info(cursor, 'form_field_templates')
    expected_columns = ['id', 'form_type', 'field_name', 'field_label', 'field_type', 
                       'field_options', 'validation_rules', 'default_value', 'placeholder', 
                       'help_text', 'sort_order', 'is_required', 'is_readonly', 
                       'is_visible', 'is_active', 'created_at', 'updated_at']
    
    actual_columns = [col[1] for col in columns]
    missing_columns = set(expected_columns) - set(actual_columns)
    
    if missing_columns:
        print(f"❌ 缺少字段: {missing_columns}")
        return False
    
    # 统计记录数
    record_count = count_table_records(cursor, 'form_field_templates')
    print(f"✅ form_field_templates 表结构正确，包含 {record_count} 条记录")
    
    # 检查表单类型分布
    cursor.execute("""
        SELECT form_type, COUNT(*) as count 
        FROM form_field_templates 
        GROUP BY form_type 
        ORDER BY form_type
    """)
    
    form_types = cursor.fetchall()
    print("   表单类型分布:")
    for form_type, count in form_types:
        print(f"     - {form_type}: {count} 个字段")
    
    return True

def verify_permissions_tables(cursor):
    """验证权限相关表"""
    print("\n🔐 验证权限相关表...")
    
    tables = ['permissions', 'role_permissions', 'user_permissions']
    all_exist = True
    
    for table in tables:
        if not verify_table_exists(cursor, table):
            print(f"❌ {table} 表不存在")
            all_exist = False
        else:
            record_count = count_table_records(cursor, table)
            print(f"✅ {table} 表存在，包含 {record_count} 条记录")
    
    if not all_exist:
        return False
    
    # 检查权限模块分布
    cursor.execute("""
        SELECT module, COUNT(*) as count 
        FROM permissions 
        GROUP BY module 
        ORDER BY module
    """)
    
    modules = cursor.fetchall()
    print("   权限模块分布:")
    for module, count in modules:
        print(f"     - {module}: {count} 个权限")
    
    # 检查角色权限分配
    cursor.execute("""
        SELECT role, COUNT(*) as count 
        FROM role_permissions 
        WHERE is_granted = 1
        GROUP BY role 
        ORDER BY role
    """)
    
    roles = cursor.fetchall()
    print("   角色权限分配:")
    for role, count in roles:
        print(f"     - {role}: {count} 个权限")
    
    return True

def verify_system_configs(cursor):
    """验证系统配置表"""
    print("\n⚙️ 验证 system_configs 表...")
    
    if not verify_table_exists(cursor, 'system_configs'):
        print("❌ system_configs 表不存在")
        return False
    
    # 检查字段结构
    columns = get_table_info(cursor, 'system_configs')
    expected_columns = ['id', 'config_key', 'config_value', 'config_type', 'category', 
                       'name', 'description', 'default_value', 'validation_rule', 
                       'is_public', 'is_editable', 'sort_order', 'created_at', 'updated_at']
    
    actual_columns = [col[1] for col in columns]
    missing_columns = set(expected_columns) - set(actual_columns)
    
    if missing_columns:
        print(f"❌ 缺少字段: {missing_columns}")
        return False
    
    # 统计记录数
    record_count = count_table_records(cursor, 'system_configs')
    print(f"✅ system_configs 表结构正确，包含 {record_count} 条记录")
    
    # 检查配置分类分布
    cursor.execute("""
        SELECT category, COUNT(*) as count 
        FROM system_configs 
        GROUP BY category 
        ORDER BY category
    """)
    
    categories = cursor.fetchall()
    print("   配置分类分布:")
    for category, count in categories:
        print(f"     - {category}: {count} 个配置项")
    
    return True

def generate_verification_report(results):
    """生成验证报告"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    report_file = os.path.join(current_dir, 'verification_report.txt')
    
    with open(report_file, 'w', encoding='utf-8') as f:
        f.write(f"数据库表结构验证报告\n")
        f.write(f"验证时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
        f.write(f"{'='*50}\n\n")
        
        total_tables = len(results)
        passed_tables = sum(1 for result in results.values() if result)
        
        f.write(f"验证结果概览:\n")
        f.write(f"总计表数: {total_tables}\n")
        f.write(f"验证通过: {passed_tables}\n")
        f.write(f"验证失败: {total_tables - passed_tables}\n\n")
        
        f.write(f"详细结果:\n")
        for table_name, result in results.items():
            status = "✅ 通过" if result else "❌ 失败"
            f.write(f"- {table_name}: {status}\n")
        
        if all(results.values()):
            f.write(f"\n🎉 所有表验证通过！数据库优化成功完成。\n")
        else:
            f.write(f"\n⚠️ 部分表验证失败，请检查相关迁移脚本。\n")
    
    print(f"\n📄 验证报告已保存到: {report_file}")

def main():
    """主函数"""
    print("🔍 开始验证数据库表结构")
    print(f"验证时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    
    db_path = get_db_path()
    
    if not os.path.exists(db_path):
        print(f"❌ 数据库文件不存在: {db_path}")
        return
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        print(f"✅ 成功连接到数据库: {db_path}")
        
        # 执行各项验证
        verification_results = {
            'system_dictionaries': verify_system_dictionaries(cursor),
            'material_categories': verify_material_categories(cursor),
            'form_field_templates': verify_form_field_templates(cursor),
            'permissions_tables': verify_permissions_tables(cursor),
            'system_configs': verify_system_configs(cursor)
        }
        
        conn.close()
        
        # 输出总结
        print(f"\n{'='*60}")
        print("🎯 验证完成")
        print(f"{'='*60}")
        
        total_checks = len(verification_results)
        passed_checks = sum(1 for result in verification_results.values() if result)
        
        print(f"总计验证: {total_checks} 项")
        print(f"验证通过: {passed_checks} 项")
        print(f"验证失败: {total_checks - passed_checks} 项")
        
        if all(verification_results.values()):
            print("\n🎉 所有表结构验证通过！")
            print("\n✨ 数据库优化已成功完成，包括:")
            print("  1. ✅ 系统字典表 - 统一选项数据管理")
            print("  2. ✅ 材料分类表 - 层级分类支持")
            print("  3. ✅ 表单模板表 - 动态表单配置")
            print("  4. ✅ 权限管理表 - 细粒度权限控制")
            print("  5. ✅ 系统配置表 - 参数化配置")
        else:
            print("\n❌ 部分表验证失败:")
            for table_name, result in verification_results.items():
                if not result:
                    print(f"  - {table_name}")
            print("\n建议检查相关迁移脚本并重新执行")
        
        # 生成验证报告
        generate_verification_report(verification_results)
        
    except Exception as e:
        print(f"❌ 验证过程中发生错误: {str(e)}")

if __name__ == '__main__':
    main()